Caused by: java.sql.SQLException: ORA

2024-02-13 20:35| 来源: 网络整理| 查看: 265

Caused by: java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000解决方案

Oracle查询语句中in list集合中最大不能超过1000，这是其语法限制，我们可以通过以下方法解决：

1.将List均分成n份

主要使用averageAssign函数将routeTypeList分成n等份，用lists集合装

public ResultVO list(){ //1. 查询所有routeStep List routeStepList = routeStepService.findAll(); List routeTypeList = routeStepList.stream() .map(e -> e.getRoute()) .collect(Collectors.toList()); int n = (routeTypeList.size() / 1000) + 1 ; System.out.println(n); List lists = averageAssign(routeTypeList, n); List route1List = routeService.findByRouteIn(lists); route1List = route1List.stream().distinct().collect(Collectors.toList()); ... ... } /** * 将一个list均分成n个list,主要通过偏移量来实现的 * * @param source * @return */ public static List averageAssign(List source, int n) { List result = new ArrayList(); int remaider = source.size() % n; //(先计算出余数) int number = source.size() / n; //然后是商 int offset = 0;//偏移量 for (int i = 0; i value = source.subList(i * number + offset, (i + 1) * number + offset + 1); remaider--; offset++; } else { value = source.subList(i * number + offset, (i + 1) * number + offset); } result.add(value); } return result; } 2.使用google guava对List进行分割 //使用guava对list进行分割 List users = userService.findAll(); //按每50个一组分割 List parts = Lists.partition(users, 50); parts.stream().forEach(list -> { process(list); }); 3.使用apache common collection List intList = Lists.newArrayList(1, 2, 3, 4, 5, 6, 7, 8); List subs = ListUtils.partition(intList, 3); 4.java8 Stream 大数据量List分批处理 //按每3个一组分割 private static final Integer MAX_NUMBER = 3; /** * 计算切分次数 */ private static Integer countStep(Integer size) { return (size + MAX_NUMBER - 1) / MAX_NUMBER; } public static void main(String[] args) { List list = Arrays.asList(1, 2, 3, 4, 5, 6, 7); int limit = countStep(list.size()); //方法一：使用流遍历操作 List mglist = new ArrayList(); Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> { mglist.add(list.stream().skip(i * MAX_NUMBER).limit(MAX_NUMBER).collect(Collectors.toList())); }); System.out.println(mglist); //方法二：获取分割后的集合 List splitList = Stream.iterate(0, n -> n + 1).limit(limit).parallel().map(a -> list.stream().skip(a * MAX_NUMBER).limit(MAX_NUMBER).parallel().collect(Collectors.toList())).collect(Collectors.toList()); System.out.println(splitList); }

【本文地址】

公司简介

联系我们